﻿'Description:材料申领相关业务
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:longb
'create Date:2022-07-11
'Modify by longb
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Public Class NotProductMaterialApplyBillBusiness
    ''' <summary>
    ''' 申领单---(分页)查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetAuxMaterialApplyBill(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                     SELECT  bill.ZSNPMATERIALAPPLYBILLID ,bill.ZSNPMATERIALAPPLYBILLNO ,a.MATERIALCODE ,a.MATERIALDESC ,bill.APPLYQTY 
                    ,um.UOMNAME,bill.TOGETHERFLAG,w.WORKFLOWSTEPNAME ,bill.PURPOSE,decode(bill.RETURNREASON,null,'',bill.RETURNREASON) RETURNREASON
                    ,decode(bill.AUDITRETURNREASON,null,'',bill.AUDITRETURNREASON) AUDITRETURNREASON,bill.UOMID,bill.WORKFLOWSTEPID
                    ,e.FULLNAME ApplyNAME,to_char(bill.APPLYDATE,'yyyy-MM-dd hh24:mi:ss')APPLYDATE
                    ,e2.FULLNAME CONFIRMNMAE,to_char(bill.CONFIRMDATE ,'yyyy-MM-dd hh24:mi:ss')CONFIRMDATE
                    ,e3.FULLNAME AUDITNAME,to_char(bill.AUDITDATE ,'yyyy-MM-dd hh24:mi:ss')AUDITDATE
                    ,e4.FULLNAME OUTSTOREEMPLOYEENAME,to_char(bill.OUTSTOREDATE,'yyyy-MM-dd hh24:mi:ss')OUTSTOREDATE,bill.SENDOUTQTY,um1.UOMNAME SENDOUTUOM
                    ,DECODE(bill.STATUS,'0','待提交','1','待确认','2','待审核','3','已审核','4','已退回','5','已收料','其他') STATUS
                    FROM ZSNPMaterialApplyBill bill
                    LEFT JOIN ZsNotProductMaterial a ON a.MATERIALCODE =bill.MATERIALCODE
                    LEFT JOIN UOM um ON um.UOMID =bill.UOMID 
                    LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =bill.WORKFLOWSTEPID 
                    LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =bill.APPLYEMPLOYEEID 
                    LEFT JOIN EMPLOYEE e2 ON e2.EMPLOYEEID =bill.CONFIRMEMPLOYEEID 
                    LEFT JOIN EMPLOYEE e3 ON e3.EMPLOYEEID =bill.AUDITEMPLOYEEID
                    LEFT JOIN EMPLOYEE e4 ON e4.EMPLOYEEID =bill.OUTSTOREEMPLOYEEID
                    LEFT JOIN UOM um1 ON um1.UOMID =bill.SENDOUTUOMID 
                     WHERE 1=1
                      </string>.Value)
        If para.ContainsKey("Status") Then '状态
            If Not String.IsNullOrWhiteSpace(para("Status")) Then
                sb.AppendFormat(" and bill.STATUS='{0}' ", para("Status"))
            End If
        End If
        If para.ContainsKey("NPmaterialName") Then '
            If Not String.IsNullOrWhiteSpace(para("NPmaterialName")) Then
                sb.AppendFormat(" and a.MATERIALCODE like '%{0}%' ", para("NPmaterialName"))
            End If
        End If
        If para.ContainsKey("Description") Then '
            If Not String.IsNullOrWhiteSpace(para("Description")) Then
                sb.AppendFormat(" and a.MATERIALDESC like '%{0}%' ", para("Description"))
            End If
        End If
        If para.ContainsKey("startedate") Then
            If Not String.IsNullOrWhiteSpace(para("startedate")) Then
                sb.AppendFormat(" and to_char(bill.APPLYDATE,'yyyy-MM-dd')>='{0}' ", para("startedate"))
            End If
        End If
        If para.ContainsKey("enddate") Then
            If Not String.IsNullOrWhiteSpace(para("enddate")) Then
                sb.AppendFormat(" and to_char(bill.APPLYDATE,'yyyy-MM-dd')<='{0}' ", para("enddate"))
            End If
        End If
        If para.ContainsKey("WorkcenterID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkcenterID")) Then
                sb.AppendFormat(" and bill.WorkcenterID='{0}' ", para("WorkcenterID"))
            End If
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 申领单---新增
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertAuxMaterialApplyBill(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim sb As New StringBuilder
        sb.AppendLine("INSERT INTO ZSNPMaterialApplyBill(ZSNPMATERIALAPPLYBILLID,ZSNPMATERIALAPPLYBILLNO,MATERIALCODE,APPLYQTY,UOMID,TOGETHERFLAG,WORKFLOWSTEPID,PURPOSE,APPLYDATE,APPLYEMPLOYEEID,STATUS,WORKCENTERID,TEAMID)")
        sb.AppendFormat("VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',sysdate,'{8}','0','{9}','{10}')", para("ZSNPMATERIALAPPLYBILLID"), para("ZSNPMATERIALAPPLYBILLNO"), para("MATERIALCODE"), para("APPLYQTY"), para("UOMID"), para("TOGETHERFLAG"), para("WORKFLOWSTEPID"), para("PURPOSE"), para("APPLYEMPLOYEEID"), para("WORKCENTERID"), para("TEAMID"))
        Return DatabaseHelper.ExecuteSql(sb.ToString) > 0
    End Function
    ''' <summary>
    ''' 申领单---更新
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateAuxMaterialApplyBill(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim sb As New StringBuilder
        sb.AppendLine(" UPDATE ZSNPMaterialApplyBill SET")
        If para.ContainsKey("APPLYQTY") Then '保存更新
            sb.AppendFormat("STATUS='0',APPLYQTY='{0}',UOMID='{1}',TOGETHERFLAG='{2}',WORKFLOWSTEPID='{3}',PURPOSE='{4}'", para("APPLYQTY"), para("UOMID"), para("TOGETHERFLAG"), para("WORKFLOWSTEPID"), para("PURPOSE"))
        End If
        If para.ContainsKey("CONFIRMEMPLOYEEID") Then '确认更新
            sb.AppendFormat("STATUS='2',CONFIRMDATE=sysdate,CONFIRMEMPLOYEEID='{0}'", para("CONFIRMEMPLOYEEID"))
        End If
        If para.ContainsKey("AUDITEMPLOYEEID") Then '审核、完更新
            sb.AppendFormat("STATUS='3',AUDITDATE=sysdate,AUDITEMPLOYEEID='{0}'", para("AUDITEMPLOYEEID"))
        End If
        If para.ContainsKey("RETURNREASON") Then '确认退回
            sb.AppendFormat("STATUS='4',RETURNREASON='{0}'", para("RETURNREASON"))
        End If
        If para.ContainsKey("AUDITRETURNREASON") Then '审核退回
            sb.AppendFormat("STATUS='4',AUDITRETURNREASON='{0}'", para("AUDITRETURNREASON"))
        End If
        If para.ContainsKey("Status") Then '更新状态
            sb.AppendFormat("Status='{0}'", para("Status"))
        End If
        If para.ContainsKey("delete") Then '更新状态
            sb = New StringBuilder
            sb.AppendLine(" DELETE from  ZSNPMaterialApplyBill")
        End If
        sb.AppendFormat(" WHERE ZSNPMATERIALAPPLYBILLID IN ('{0}')", para("ZSNPMATERIALAPPLYBILLID").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(sb.ToString) > 0
    End Function

    ''' <summary>
    ''' 查询当天已申领记录数
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetAuxMaterialApplyCount() As String
        Dim Count = "1"
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT COUNT(1)+1 CN FROM ZSNPMaterialApplyBill WHERE to_char(APPLYDATE,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Count = dt.Rows("0")("CN").ToString
        End If
        Return Count
    End Function
    ''' <summary>
    ''' 查询所有材料信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetAuxMaterial(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT MATERIALCODE ID,MATERIALDESC||'('||MATERIALCODE||')' NAME,MATERIALDESC FROM ZsNotProductMaterial WHERE MATERIALDESC LIKE '%{0}%' OR MATERIALCODE LIKE '%{0}%'", para("Name"))
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询所有单位
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetUom() As DataTable
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT UOMID ID,UOMNAME NAME,DESCRIPTION FROM UOM")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询所有任务单号
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerNo(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT DISTINCT WORKFLOWID ID,CONTAINERNO NAME FROM ENGINEPARTPLAN WHERE  CONTAINERNO IS NOT NULL AND CONTAINERNO LIKE '%{0}%'", para("Name"))
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' （分页）查询按年统计材料消耗
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSumAuxMaterialByYear(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                      SELECT a.MATERIALCODE ,a.MATERIALDESC,w.WORKCENTERNAME ,t.TEAMNAME,to_char(bill.APPLYDATE,'yyyy') APPLYDATE,SUM(BILL.APPLYQTY) APPLYQTY,nvl(yc.USEQUOTA ,0) quotaQTY
                    FROM ZSNPMaterialApplyBill bill
                    LEFT JOIN ZsNotProductMaterial a ON a.MATERIALCODE =bill.MATERIALCODE 
                    LEFT JOIN WORKCENTER w ON w.WORKCENTERID =bill.WORKCENTERID 
                    LEFT JOIN TEAM t ON t.TEAMID =bill.TEAMID
                    LEFT JOIN ZsNotProductMaterial yc 
                    ON yc.MATERIALCODE=bill.MATERIALCODE AND to_char(yc.CONSUMEYEAR)=to_char(bill.APPLYDATE,'yyyy') 
                     AND yc.SHIFTID =bill.TEAMID
                    WHERE 1=1
                      </string>)
        If para.ContainsKey("NPmaterialName") Then '
            If Not String.IsNullOrWhiteSpace(para("NPmaterialName")) Then
                sb.AppendFormat(" and a.MATERIALCODE like '%{0}%' ", para("NPmaterialName"))
            End If
        End If
        If para.ContainsKey("Description") Then '
            If Not String.IsNullOrWhiteSpace(para("Description")) Then
                sb.AppendFormat(" and a.MATERIALDESC like '%{0}%' ", para("Description"))
            End If
        End If
        If para.ContainsKey("WorkcenterID") Then '
            If Not String.IsNullOrWhiteSpace(para("WorkcenterID")) Then
                sb.AppendFormat(" and bill.WorkcenterID='{0}'", para("WorkcenterID"))
            End If
        End If
        If para.ContainsKey("TeamID") Then '
            If Not String.IsNullOrWhiteSpace(para("TeamID")) Then
                sb.AppendFormat(" and bill.TeamID ='{0}'", para("TeamID"))
            End If
        End If
        If para.ContainsKey("yearmonth") Then '
            If Not String.IsNullOrWhiteSpace(para("yearmonth")) Then
                sb.AppendFormat(" and to_char(bill.APPLYDATE,'yyyy')='{0}'", para("yearmonth"))
            End If
        End If
        sb.AppendLine("GROUP BY  a.MATERIALCODE,a.MATERIALDESC,w.WORKCENTERNAME ,t.TEAMNAME,to_char(bill.APPLYDATE,'yyyy'),yc.USEQUOTA")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 查询按月统计材料消耗
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSumAuxMaterialByMonth(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                   SELECT to_char(bill.APPLYDATE,'mm') APPLYDATE,SUM(BILL.APPLYQTY) APPLYQTY,nvl(YC.USEQUOTA,0) quotaQTY
                    FROM ZSNPMaterialApplyBill bill
                    LEFT JOIN (
                    	 SELECT yc.MATERIALCODE,yc.SHIFTID TEAMID,ym.CONSUMEMONTH MONTH,ym.USEQUOTA FROM ZsNotProductMaterial yc
                    LEFT JOIN ZsNotProductMaterialDetail ym ON ym.ZSNOTPRODUCTMATERIALID=yc.ZSNOTPRODUCTMATERIALID 
                    )yc 
                    ON yc.MATERIALCODE=bill.MATERIALCODE AND yc.MONTH=TO_NUMBER(to_char(bill.APPLYDATE,'mm')) 
                    AND yc.TEAMID=bill.TEAMID
                    WHERE 1=1  
                      </string>)

        If para.ContainsKey("yearmonth") Then '
            If Not String.IsNullOrWhiteSpace(para("yearmonth")) Then
                sb.AppendFormat(" and to_char(bill.APPLYDATE,'yyyy')='{0}'", para("yearmonth"))
            End If
        End If
        'If para.ContainsKey("WorkcenterID") Then '
        '    If Not String.IsNullOrWhiteSpace(para("WorkcenterID")) Then
        '        sb.AppendFormat(" and bill.WorkcenterID='{0}'", para("WorkcenterID"))
        '    End If
        'End If
        If para.ContainsKey("IDS") Then '
            If Not String.IsNullOrWhiteSpace(para("IDS")) Then
                sb.AppendFormat(" and bill.MATERIALCODE IN('{0}')", para("IDS").Replace(",", "','"))
            End If
        End If
        sb.AppendLine("GROUP BY  to_char(bill.APPLYDATE,'mm'),YC.USEQUOTA order By  to_char(bill.APPLYDATE,'mm')")
        Return (DatabaseHelper.Query_ReturnDatTable(sb.ToString))
    End Function
    ''' <summary>
    ''' 年、月定额消耗设定--新增
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AddYearAndMonthConsumeQuota(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim sb As New StringBuilder
        If para.ContainsKey("Type") Then
            If para("Type") = 0 Then
                sb.AppendLine("INSERT INTO ZsNotProductMaterial(ZSNOTPRODUCTMATERIALID,MATERIALCODE,MATERIALDESC,SHIFTID,CONSUMEYEAR,USEQUOTA,CREATEDATE,CREATEEMPLOYEEID)")
                sb.AppendFormat("VALUES('{0}','{1}','{2}','{3}','{4}','{5}',sysdate,'{6}')", para("ZSNOTPRODUCTMATERIALID"), para("MaterialCode"), para("MaterialDesc"), para("ShiftID"), para("Year"), para("YearQty"), para("CREATEEMPLOYEEID"))
            End If
            If para("Type") = 1 Then
                sb.AppendLine("INSERT INTO ZsNotProductMaterialDetail(ZSNOTPRODUCTMATERIALDETAILID,ZSNOTPRODUCTMATERIALID,CONSUMEMONTH,USEQUOTA,CREATEDATE,CREATEEMPLOYEEID,NOTES)")
                sb.AppendFormat("VALUES('{0}','{1}','{2}','{3}',sysdate,'{4}','{5}')", para("ZSNOTPRODUCTMATERIALDETAILID"), para("ZSNOTPRODUCTMATERIALID"), para("Month"), para("MonthQty"), para("CREATEEMPLOYEEID"), para("Notes"))
            End If
        End If
        Return DatabaseHelper.ExecuteSql(sb.ToString) > 0
    End Function
    ''' <summary>
    ''' 年、月定额消耗设定--更新（量、时间）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateYearAndMonthConsumeQuota(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim arr As New ArrayList()
        Dim sb As New StringBuilder
        If Not String.IsNullOrWhiteSpace(para("ZSNOTPRODUCTMATERIALID")) Then
            sb.AppendFormat("UPDATE ZsNotProductMaterial SET USEQUOTA='{0}',MATERIALDESC='{1}',CREATEDATE=sysdate WHERE ZSNOTPRODUCTMATERIALID='{2}'", para("YearQty"), para("MaterialDesc"), para("ZSNOTPRODUCTMATERIALID"))
            arr.Add(sb.ToString)
        End If
        If Not String.IsNullOrWhiteSpace(para("ZSNOTPRODUCTMATERIALDETAILID")) Then
            sb = New StringBuilder
            sb.AppendFormat("UPDATE ZsNotProductMaterialDetail SET USEQUOTA='{0}',CREATEDATE=sysdate,NOTES='{1}' WHERE ZSNOTPRODUCTMATERIALDETAILID='{2}'", para("MonthQty"), para("Notes"), para("ZSNOTPRODUCTMATERIALDETAILID"))
            arr.Add(sb.ToString)
        End If
        If arr.Count > 0 Then
            Return DatabaseHelper.ExecuteSqlListTran(arr)
        End If
        Return False
    End Function
    ''' <summary>
    ''' 年、月定额消耗设定--删除
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DeleteYearAndMonthConsumeQuota(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim arr As New ArrayList()
        Dim sb As New StringBuilder
        If para("Type") = 0 Then
            sb.AppendFormat("DELETE FROM ZsNotProductMaterial  WHERE ZSNOTPRODUCTMATERIALID IN('{0}')", para("MIDS").Replace(",", "','"))
            arr.Add(sb.ToString)
            sb = New StringBuilder
            sb.AppendFormat("DELETE FROM ZsNotProductMaterialDetail WHERE ZSNOTPRODUCTMATERIALID IN('{0}')", para("MIDS").Replace(",", "','"))
            arr.Add(sb.ToString)
        End If
        If para("Type") = 1 Then
            sb = New StringBuilder
            sb.AppendFormat("DELETE FROM ZsNotProductMaterialDetail WHERE ZSNOTPRODUCTMATERIALDETAILID IN('{0}')", para("IDS").Replace(",", "','"))
            arr.Add(sb.ToString)
        End If
        If arr.Count > 0 Then
            Return DatabaseHelper.ExecuteSqlListTran(arr)
        End If
        Return False
    End Function

    ''' <summary>
    ''' 年定额消耗设定--(分页)查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetYearConsumeQuota(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                    SELECT Yc.MATERIALCODE,yc.MATERIALDESC,t.TEAMNAME,yc.CONSUMEYEAR YEAR,yc.USEQUOTA YEARQTY,e.FULLNAME,w.WORKCENTERNAME, 
                    to_char(yc.CREATEDATE,'yyyy-MM-dd hh24:mm:ss')CREATEDATE,yc.ZSNOTPRODUCTMATERIALID,yc.SHIFTID TEAMID,t.WORKCENTERID
                    FROM ZsNotProductMaterial yc
                    LEFT JOIN TEAM t ON t.TEAMID =yc.SHIFTID 
                    LEFT JOIN WORKCENTER w ON w.WORKCENTERID =t.WORKCENTERID 
                    LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =yc.CREATEEMPLOYEEID
                    WHERE 1=1
                      </string>)
        If para.ContainsKey("AuxmaterialName") Then '
            If Not String.IsNullOrWhiteSpace(para("AuxmaterialName")) Then
                sb.AppendFormat(" and Yc.MATERIALCODE like '%{0}%' ", para("AuxmaterialName"))
            End If
        End If
        If para.ContainsKey("Description") Then '
            If Not String.IsNullOrWhiteSpace(para("Description")) Then
                sb.AppendFormat(" and yc.MATERIALDESC like '%{0}%' ", para("Description"))
            End If
        End If
        If para.ContainsKey("WorkcenterID") Then '
            If Not String.IsNullOrWhiteSpace(para("WorkcenterID")) Then
                sb.AppendFormat(" and yc.WorkcenterID='{0}'", para("WorkcenterID"))
            End If
        End If
        If para.ContainsKey("TeamID") Then '
            If Not String.IsNullOrWhiteSpace(para("TeamID")) Then
                sb.AppendFormat(" and yc.SHIFTID ='{0}'", para("TeamID"))
            End If
        End If
        If para.ContainsKey("yearmonth") Then '
            If Not String.IsNullOrWhiteSpace(para("yearmonth")) Then
                sb.AppendFormat(" and yc.CONSUMEYEAR='{0}'", para("yearmonth"))
            End If
        End If
        sb.AppendLine(" ORDER BY yc.CONSUMEYEAR,yc.CREATEDATE")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 月定额消耗设定--查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMonthConsumeQuota(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                 SELECT ym.ZSNOTPRODUCTMATERIALDETAILID,ym.CONSUMEMONTH MONTH,ym.USEQUOTA MONTHQTY,ym.NOTES,
                e.FULLNAME,to_char(ym.CREATEDATE,'yyyy-MM-dd hh24:mm:ss')CREATEDATE
                FROM ZsNotProductMaterialDetail ym
                LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =ym.CREATEEMPLOYEEID
                      </string>)
        sb.AppendFormat("WHERE ZSNOTPRODUCTMATERIALID IN ('{0}')", para("IDS").Replace(",", "','"))
        sb.AppendLine(" ORDER BY ym.CONSUMEMONTH,ym.CREATEDATE")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 材料申领单--收料确认
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ReciveConfirmAuxMaterialApplyBill(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim arr As New ArrayList
        Dim sb As New StringBuilder
        sb.AppendFormat("UPDATE ZSNPMaterialApplyBill SET STATUS='5' WHERE ZSNPMATERIALAPPLYBILLID IN('{0}')", para("IDS")) '更新申领单为【已接收】
        arr.Add(sb.ToString)
        sb = New StringBuilder
        sb.AppendFormat("UPDATE ZsRequiredToolsTransport SET STATUS='20' WHERE ZsRequiredToolsTransportID IN('{0}')", para("IDS")) '更新临时配送为【已完成】
        arr.Add(sb.ToString)
        Return DatabaseHelper.ExecuteSqlListTran(arr)
    End Function
End Class
